﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
namespace DAO
{
    public class KhachHangDAO
    {
        public List<KhachHangDTO> LayDanhSachKhachHang()
        {
            List<KhachHangDTO> listKH = new List<KhachHangDTO>();
            string sql = "Select * From KHACHHANG";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                KhachHangDTO khDTO = new KhachHangDTO();
                khDTO.MaKhachHang = reader.GetInt32(0);
                khDTO.TenKhachHang = reader.GetString(1);
                khDTO.SoDienThoai = reader.GetString(2);
                khDTO.Email = reader.GetString(3);
                khDTO.DiaChi = reader.GetString(4);
                listKH.Add(khDTO);
            }
            conn.Close();
            return listKH;
        }
        public string ThemKhachHang(KhachHangDTO khDTO)
        {
            string mess = "";

            string sql = "Insert into ";
            sql += " KhachHang(MaKhachHang, TenKhachHang, SoDienThoai, Email, DiaChi)" ;
            sql += " values(@MaKhachHang, @TenKhachHang, @SoDienThoai, @Email, @DiaChi)";
           
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlParameter para;
                para = cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                para.Value = khDTO.MaKhachHang;
                para = cmd.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar);
                para.Value = khDTO.TenKhachHang;
                para = cmd.Parameters.Add("@SoDienThoai", SqlDbType.VarChar);
                para.Value = khDTO.SoDienThoai;
                para = cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
                para.Value = khDTO.Email;
                para = cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
                para.Value = khDTO.DiaChi;
                cmd.ExecuteNonQuery();
                conn.Close();
           }
           catch (SqlException ex)
           {
              mess = ex.Message;
           }
           finally
           {
              conn.Close();
           }
           return mess;
        }
        public int LayMaKhachHang()
        {
            int max = 0;
            List<KhachHangDTO> listKH = LayDanhSachKhachHang();
            if (listKH.Count < 1)
                return max;
            for (int i = 0; i < listKH.Count; i++)
            {
                if (listKH[i].MaKhachHang > max)
                    max = listKH[i].MaKhachHang;
            }
            return max;
        }
        public int KiemTraTonTaiKhachHang(string tenkh, string sdt, string diachi)
        {
            int ma = 0;
            string sql = "Select MaKhachHang From KHACHHANG where TenKhachHang = N'" + tenkh + "' and SoDienThoai = '" + sdt + "'";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                ma = reader.GetInt32(0);
            conn.Close();
            string sql2 = "Select MaKhachHang From KHACHHANG where TenKhachHang = N'" + tenkh + "' and DiaChi = N'" + diachi + "'";
            SqlConnection conn2 = DataProvider.ConnectDB();
            SqlCommand cmd2 = new SqlCommand(sql2, conn2);
            SqlDataReader reader2 = cmd2.ExecuteReader();
            while (reader2.Read())
                ma = reader2.GetInt32(0);
            conn2.Close();
            return ma;
        }
        public string XoaKhachHang(int maKH)
        {
            string mess = "";
            string sql = "Delete * From KHACHHANG where MaKhachHang = " + maKH;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
    }
}
